-- exec sp_get_all_pending_transit_lots

Create  proc sp_get_all_pending_transit_lots as 
BEGIN	
	DECLARE @results table ( Lot_Id int, Lot_Number Varchar(20), ProcessStep_Id int, 
	Location_Id int, Customer_PO varchar(50), Customer_SO varchar(50), Invoice_Number varchar(50),
	Dest_Location Varchar(50), Date_In DateTime, Qty_In int, Source_Location varchar(50), MAWB_Number varchar(50),
	HAWB_Number varchar(50), Flight_Number varchar(50), ETD varchar(50), ETA varchar(50), Part_Number varchar(50), Remarks Varchar(200) )
	
	DECLARE @lot_Id bigint
	DECLARE @lot_Number Varchar(50)
	DECLARE @processStep_Id bigint
	DECLARE @location_Id bigint
	DECLARE @customer_PO varchar(50)
	DECLARE @customer_SO varchar(50)
	DECLARE @invoice_No varchar(50)
	DECLARE @nextLocation varchar(50)
	DECLARE @shipDate datetime
	DECLARE @shipQty bigint
	DECLARE @shipTime int
	DECLARE	@MAWB_Number varchar(50)
	DECLARE	@HAWB_Number varchar(50)
	DECLARE	@Flight_Number varchar(50)
	DECLARE	@ETD varchar(50)
	DECLARE	@ETA varchar(50)
	DECLARE @presentDate datetime
	DECLARE @transitTime int
	DECLARE @sourceLocation varchar(50)
	DECLARE @partNumber varchar(50)
	DECLARE @remarks varchar(200)

	DECLARE @transit_cursor CURSOR

	SET @transit_cursor = CURSOR FOR SELECT TLT_Lot_Id, Lot_Table.Lot_Number, Lot_Table.ProcessStep_ID, 
	Lot_table.Location_ID, TLT_Customer_PO, TLT_Customer_SO, TLT_Invoice_Number, 
	TLT_NextLocation, TLT_Ship_Date, TLT_Ship_Qty, TLT_MAWB_Number, TLT_HAWB_Number, TLT_Flight_Number, TLT_ETD, TLT_ETA
	FROM Transit_lot_Table, Shipped_Lot, Lot_Table WHERE Transit_lot_Table.ShippedLot_ID = Shipped_Lot.ShippedLot_ID 
	and Shipped_Lot.Lot_Id = Lot_Table.Lot_Id
	and Status = 'TRANSIT' ORDER BY TLT_Ship_Date

	SET @presentDate = GETDATE()
	OPEN @transit_cursor
	
	FETCH NEXT FROM @transit_cursor INTO @lot_Id, @lot_Number, @processStep_Id, @location_Id, @customer_PO, 
	@customer_SO, @invoice_No, @nextLocation, @shipDate, @shipQty, @MAWB_Number, @HAWB_Number, @Flight_Number, @ETD, @ETA

	WHILE @@FETCH_STATUS = 0
	BEGIN
		/*
		  We are selecting the max Shiptime from this location for caliculation of Ship time
		  because we dont know the actual Destination Location 
		  If no transportation time is provided we are taking 5 days as the default Transportation Time
		  This default value needs to be moved to configuration table	
		*/
		SET @shipTime = NULL
		SELECT @shipTime = MAX( TT_Normal_Time ) FROM Transportation_table WHERE ProcessStep_Id = @processStep_Id and TT_Source_Location_ID = @location_Id

		/*IF @shipTime IS NULL
		BEGIN
			SET @shipTime = 5
		END*/
		
		SELECT @transitTime = DATEDIFF( day, @shipDate, @presentDate )
		
		IF @shipTime IS Not NULL
		BEGIN
			IF @transitTime > @shipTime
			BEGIN
				SELECT @sourceLocation = L_Vendor_Name + '/' + City FROM Location, Address WHERE Location.Address_Id = Address.Address_Id AND Location_ID = @location_Id
				SELECT @partNumber = Part_Number FROM PRODUCT WHERE ProcessStep_Id = @processStep_Id
	
				INSERT INTO @results ( Lot_Id, Lot_Number, ProcessStep_Id, Location_Id, Customer_PO, 
				Customer_SO, Invoice_Number, Dest_Location, Date_In, Qty_In, Source_Location, MAWB_Number,
				HAWB_Number, Flight_Number, ETD, ETA, Part_Number, Remarks ) VALUES
				( @lot_Id, @lot_Number, @processStep_Id, @location_Id, @customer_PO, 
				@customer_SO, @invoice_No, @nextLocation, @shipDate, @shipQty, @sourceLocation, @MAWB_Number, @HAWB_Number, @Flight_Number, @ETD, @ETA, @partNumber, '' )
			END
		END
		ELSE
		BEGIN
			SET @remarks = 'Lot is in Transit for '+ CONVERT( CHAR(4), @transitTime) + ' days'
			SELECT @sourceLocation = L_Vendor_Name + '/' + City FROM Location, Address WHERE Location.Address_Id = Address.Address_Id AND Location_ID = @location_Id
			SELECT @partNumber = Part_Number FROM PRODUCT WHERE ProcessStep_Id = @processStep_Id
			
			INSERT INTO @results ( Lot_Id, Lot_Number, ProcessStep_Id, Location_Id, Customer_PO, 
			Customer_SO, Invoice_Number, Dest_Location, Date_In, Qty_In, Source_Location, MAWB_Number,
			HAWB_Number, Flight_Number, ETD, ETA, Part_Number, Remarks ) VALUES
			( @lot_Id, @lot_Number, @processStep_Id, @location_Id, @customer_PO, 
			@customer_SO, @invoice_No, @nextLocation, @shipDate, @shipQty, @sourceLocation, @MAWB_Number, @HAWB_Number, @Flight_Number, @ETD, @ETA, @partNumber, @remarks )
		END

		FETCH NEXT FROM @transit_cursor INTO @lot_Id, @lot_Number, @processStep_Id, @location_Id, @customer_PO, 
		@customer_SO, @invoice_No, @nextLocation, @shipDate, @shipQty, @MAWB_Number, @HAWB_Number, @Flight_Number, @ETD, @ETA
	END

	SELECT * FROM @results
END